﻿using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Reflection;

namespace DataBaseHelper
{
    /// <summary>
    /// 读取数据数库 xueliang@2012-09-08
    /// </summary>
    public abstract class DataBaseHelper
    {
        public virtual IDbConnection Connection { set; get; }
        public virtual IDataReader ExecuteQuery(string sql)
        {
            return ExecuteQuery(this.Connection, sql);
        }
        public virtual IDataReader ExecuteQuery(IDbConnection conn, string sql)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd.ExecuteReader();
        }

        public virtual List<T> ExecuteQuery<T>(string sql)
        {
            var reader = ExecuteQuery(sql);
            return ToList<T>(reader);
        }
        public virtual object ExecuteScalar(IDbConnection conn, string sql)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentException("sql is null or empty");
            }
            var cmd = conn.CreateCommand();
            cmd.CommandTimeout = int.MaxValue;
            cmd.CommandText = sql;
            return cmd.ExecuteScalar();
        }
        public virtual int ExecuteScalar(string sql)
        {
            var obj = ExecuteScalar(this.Connection, sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        public virtual int ExecuteCommand(string sql)
        {
            return ExecuteCommand(this.Connection, sql);
        }
        public virtual int ExecuteCommand(IDbConnection conn, string sql)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException();
            }
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            return cmd.ExecuteNonQuery();
        }

        public virtual void ExecuteProcedure(IDbConnection conn, string procedureName, params object[] parameters)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (string.IsNullOrEmpty(procedureName))
            {
                throw new ArgumentNullException("procedure name is null");
            }
            var cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters.Length > 0)
            {
                parameters.ToList().ForEach(i => cmd.Parameters.Add(i));
            }
            cmd.ExecuteNonQuery();
        }

        #region//reflection

        public T ConvertTo<T>(IDataReader dr)
        {
            try
            {
                using (dr)
                {
                    if (dr.Read())
                    {
                        List<string> list = new List<string>(dr.FieldCount);
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            list.Add(dr.GetName(i).ToLower());
                        }
                        T model = Activator.CreateInstance<T>();
                        var properties = model.GetType().GetProperties(BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.Instance);
                        foreach (PropertyInfo pi in properties)
                        {
                            if (list.Contains(pi.Name.ToLower()))
                            {
                                if (!IsNullOrDBNull(dr[pi.Name]))
                                {
                                    pi.SetValue(model, HackType(dr[pi.Name], pi.PropertyType), null);
                                }
                            }
                        }
                        return model;
                    }
                }
                return default(T);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<T> ToList<T>(IDataReader dr)
        {
            using (dr)
            {
                List<string> field = new List<string>(dr.FieldCount);
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    field.Add(dr.GetName(i).ToLower());
                }
                List<T> list = new List<T>();
                while (dr.Read())
                {
                    T model = Activator.CreateInstance<T>();
                    foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
                    {
                        if (field.Contains(property.Name.ToLower()))
                        {
                            if (!IsNullOrDBNull(dr[property.Name]))
                            {
                                property.SetValue(model, HackType(dr[property.Name], property.PropertyType), null);
                            }
                        }
                    }
                    list.Add(model);
                }
                return list;
            }
        }
        /// <summary>
        /// 对可空类型进行判断转换，要不然会报错 
        /// </summary>
        /// <param name="value"></param>
        /// <param name="conversionType"></param>
        /// <returns></returns>
        protected object HackType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null)
                    return null;

                System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, conversionType);
        }

        protected bool IsNullOrDBNull(object obj)
        {
            return ((obj is DBNull) || string.IsNullOrEmpty(obj.ToString())) ? true : false;
        }

        #endregion
    }
}
